package com.welsmann.app.start.spider.dao.helper;

import java.math.BigInteger;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * 数据库JDBC操作辅助类
 * @author Welsmann
 *
 */
public class QueryHelper {
	
	private final static Log log = LogFactory.getLog(QueryHelper.class);

	private final static QueryRunner _queryRunner = new QueryRunner();
	
	private final static ScalarHandler _scalarHandler = new ScalarHandler() {
		@Override
		public Object handle(ResultSet rs) throws SQLException {
			Object object = super.handle(rs);
			return object instanceof BigInteger ? ((BigInteger) object)
					.longValue() : object;
		}
	};
	
	private final static List<Class<?>> _primitiveClasses = new ArrayList<Class<?>>(){
		private static final long serialVersionUID = 8520156515250608697L;
		{
			add(Long.class);
			add(Integer.class);
			add(String.class);
			add(java.util.Date.class);
			add(java.sql.Date.class);
			add(java.sql.Timestamp.class);
		}
	};
	
	private final static boolean _isPrimitive(Class<?> cls) {
		return cls.isPrimitive() || _primitiveClasses.contains(cls);
	}
	
	/**
	 * 执行JDBC单个实体查询
	 * @param beanClass 实体类
	 * @param sql 查询使用的SQL语句
	 * @param connection 数据库连接
	 * @param params 查询使用的参数
	 * @return 符合条件的单个实体
	 * @throws SQLException
	 * @since 2.3.0
	 */
	@SuppressWarnings("unchecked")
	public static <T> T single(Class<T> beanClass, String sql, Object...params) {
		Object object = null;
		try {
			object = _queryRunner.query(DBManager.getConnection(), sql, params,
					_isPrimitive(beanClass) ? _scalarHandler : new BeanHandler(
							beanClass));
		} catch (SQLException e) {
			log.error(e);
			e.printStackTrace();
		}
		return object == null ? null : (T) object;
	}
	
	/**
	 * 执行JDBC批量查询
	 * @param beanClass 实体类
	 * @param sql 查询使用的SQL语句
	 * @param connection 数据库连接
	 * @param params 查询使用的参数
	 * @return 符合条件的实体集合
	 * @throws SQLException
	 */
	@SuppressWarnings("unchecked")
	public static <T> List<T> list(Class<T> beanClass, String sql, Object... params) {
		Object object = null;
		try {
			object = _queryRunner.query(DBManager.getConnection(), sql, params,
					_isPrimitive(beanClass) ? _scalarHandler : new BeanListHandler(
							beanClass));
		} catch (SQLException e) {
			log.error(e);
			e.printStackTrace();
		}
		return object == null ? null : (List<T>) object;
	}
	
	
	/**
	 * 执行JDBC统计
	 * @param sql 统计使用的SQL
	 * @param connection 数据库连接
	 * @param params 查询使用的参数
	 * @return 统计值（Long型）
	 * @throws SQLException
	 */
	public static long count(String sql, Object...params) {
		Object object = null;
		try {
			object = _queryRunner.query(DBManager.getConnection(), sql, params,
					_scalarHandler);
		} catch (SQLException e) {
			log.error(e);
			e.printStackTrace();
		}
		return object != null ? ((Number) object).longValue() : -1L;
	}
	
	public static int update(String sql, Object...params) {
		try {
			return _queryRunner.update(DBManager.getConnection(), sql, params);
		} catch (SQLException e) {
			log.error(e);
			e.printStackTrace();
		}
		return -1;
	}
}
